﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data.Odbc;
using MySql.Data.MySqlClient;

namespace WorkerHourManager
{
    class DAL
    {
        private bool initiated = false;
        private MySqlConnectionStringBuilder connectionString;
        private MySqlConnection connection;

        public void init()
        {
            if (initiated) { return; }

            connectionString = new MySqlConnectionStringBuilder();
            connectionString.Server = "db4free.net";
            connectionString.UserID = "whm1";
            connectionString.Password = "1@3$5^";
            connectionString.Database = "whm1";

            using (connection = new MySqlConnection(connectionString.ToString()))

             connection.Open();
            initiated = true;
        }


        public void addEmployee(Employee employee)
        {
            string newCommand = "";
            newCommand += "INSERT INTO Employees VALUES(";
            newCommand += "'" + employee.id + "', ";
            newCommand += "'" + employee.firstName + "', ";
            newCommand += "'" + employee.lastName + "', ";
            newCommand += "'" + employee.Password + "', ";
            newCommand += "'" + employee.Position + "', ";
            newCommand += "'" + employee.isManager + "', ";
            newCommand += "'" + employee.isAdmin + "', ";
            newCommand += "'" + employee.managerOf + "', ";
            newCommand += "'" + employee.Team + "')";

            connection.Open();
            MySqlCommand command = new MySqlCommand(newCommand, connection);
            MySqlDataReader rdr = command.ExecuteReader();
            connection.Close();
        }

        public Employee getEmployeeById(string id)
        {
            string newCommand = "SELECT * FROM workers WHERE ID=\'" + id + "\'";

            connection.Open();
            MySqlCommand command = new MySqlCommand(newCommand, connection);
            MySqlDataReader rdr = command.ExecuteReader();

            rdr.Read();

            try
            {
                string firstName = (string)rdr["firstName"];
                string lastName = (string)rdr["lastName"];
                string Password = (string)rdr["Password"];
                string Position = (string)rdr["Position"];
                string isManager = (string)rdr["isManager"];
                string isAdmin = (string)rdr["isAdmin"];
                string managerOf = (string)rdr["managerOf"];
                string Team = (string)rdr["Team"];



                Employee employee = new Employee(id, firstName, lastName, Password, Position, isManager, isAdmin, managerOf,Team);

                connection.Close();
                return employee;
            }
            catch (InvalidOperationException e)
            {
                e.GetType();
                connection.Close();
                return null;
            }
        }

        public LinkedList<Employee> GetAllEmployees()
        {
            string newCommand = "SELECT * FROM Employees";

            connection.Open();
            MySqlCommand command = new MySqlCommand(newCommand, connection);
            MySqlDataReader rdr = command.ExecuteReader();

            LinkedList<Employee> list = new LinkedList<Employee>();

            while (rdr.Read())
            {
                string id = (string)rdr["id"];
                string firstName = (string)rdr["firstName"];
                string lastName = (string)rdr["lastName"];
                string Password = (string)rdr["Password"];
                string Position = (string)rdr["Position"];
                string isManager = (string)rdr["isManager"];
                string isAdmin = (string)rdr["isAdmin"];
                string managerOf = (string)rdr["managerOf"];
                string Team = (string)rdr["Team"];

                list.AddFirst(new Employee(id, firstName, lastName, Password, Position, isManager, isAdmin, managerOf,Team));
            }
            connection.Close();
            return list;
        }

        public void deleteEmployee(Employee e)
        {
            string newCommand = "DELETE FROM Employees WHERE Id=\'" + e.id + "\'";

            connection.Open();
            MySqlCommand command = new MySqlCommand(newCommand, connection);
            MySqlDataReader rdr = command.ExecuteReader();
            connection.Close();
        }

        public void addShift(string id, string start, string end)
        {
            string newCommand = "";
            newCommand += "INSERT INTO shifts VALUES(";
            newCommand += "'" + id + "', ";
            newCommand += "'" + start + "', ";
            newCommand += "'" + end + "')";
            Console.WriteLine(newCommand);
            connection.Open();
            MySqlCommand command = new MySqlCommand(newCommand, connection);
            MySqlDataReader rdr = command.ExecuteReader();
            connection.Close();
        }

        public LinkedList<Shift> getAllShifts(string name)
        {
            string newCommand = "SELECT * FROM shifts";

            connection.Open();
            MySqlCommand command = new MySqlCommand(newCommand, connection);
            MySqlDataReader rdr = command.ExecuteReader();

            LinkedList<Shift> list = new LinkedList<Shift>();

            while (rdr.Read())
            {
                string id = (string)rdr["id"];
                string start = (string)rdr["start"];
                string end = (string)rdr["end"];

                list.AddFirst(new Shift(id, start,end));
            }
            connection.Close();
            return list;

        }
    }
}